package com.orange.modules.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;


import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.dbhelp.page.Page;
import com.orange.modules.entity.AuthItem;
import com.orange.modules.entity.expert.AuthExpertItemMap;

@Repository
public class AuthItemRepository {
	
	@Autowired
	private DbHelper dbHelper;
	
	@Autowired
	private AuthItemDao authItemDao;
	
	/**
	 * 分页查询以及指标或者二级指标数据
	 * @param pageNum
	 * @param pageSize
	 * @param map
	 * @return
	 * @throws SQLException
	 */
	public Page<AuthItem> queryAuthItemBuPage(Integer pageNum,Integer pageSize,Map<String,String> map) throws SQLException{
		String sql="";
			sql = "SELECT TOP 1000 t.p_id as pid,t.s_content as content,t.f_id as fid,t.s_type as type," +
					" t.s_number as number ,t.s_create_time as createtime, t.s_major as major ,t.s_standards as standards "+
					" FROM  t_auth_item AS  t WHERE 1=1";
		if(StringUtils.isNotBlank(map.get("type")))
			sql+=" AND t.s_type ='"+map.get("type")+"'";
		if(StringUtils.isNotBlank(map.get("fid")))
			sql+=" AND t.f_id = '"+map.get("fid")+"'";
		if(StringUtils.isNotBlank(map.get("major")))
			sql+=" AND t.s_major = '"+map.get("major")+"'";
		sql+=" order by t.s_number asc";
		Page<AuthItem> aa = dbHelper.getPage(sql, AuthItem.class, pageNum, pageSize, new Object[] {});
		return aa;
	}
	

	
	/**
	 * 根据pid查询指标数据
	 * @param pid
	 * @return
	 * @throws SQLException
	 */
	public AuthItem queryAuthItemById(String pid) throws SQLException{
		AuthItem ai = authItemDao.findOne(pid);
		return ai;
	}
	
	/**
	 * 根据pid查询细则和等级
	 * @param itemId
	 * @return
	 * @throws SQLException
	 */
	public List<AuthItem> queryTwiceItemLevelById(String itemId,String schoolId ,String subject) throws SQLException{
		String sql = "SELECT a.event_id as pid ,a.item_id as fid ,a.event_level as level " +
				" FROM t_auth_item_level_map as a " +
				" LEFT JOIN t_auth_item as b ON a.item_id=b.p_id " +
				" WHERE a.item_id=? AND a.si_id =?  ";
		if(StringUtils.isNotBlank(subject)){
			sql+=" AND a.login_type= '"+subject+"'";
		}
		List<AuthItem> ai = dbHelper.getBeanList(sql, AuthItem.class,itemId, schoolId);
		return ai;
	}
	
	/**
	 * 根据fid查询指标数据
	 * @param pid
	 * @return
	 * @throws SQLException
	 */
	public List<AuthItem> queryAuthItemByFidAndMajor(String fid,String major) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_content as content,t.f_id as fid,t.s_type as type,t.s_number as number ,t.s_create_time as createtime ,t.s_major as major ,t.s_standards as standards "
				+ " FROM t_auth_item t WHERE t.f_id =? AND t.s_major=?";
		List<AuthItem> ai = dbHelper.getBeanList(sql, AuthItem.class, fid,major);
		return ai;
		
	}
	
	/**
	 * 根据fid,type查询指标数据
	 * @param pid
	 * @return
	 * @throws SQLException
	 */
	public List<AuthItem> queryAuthItemByFid(String fid,String type) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_content as content,t.f_id as fid,t.s_type as type,t.s_number as number ,t.s_create_time as createtime ,t.s_major as major ,t.s_standards as standards"
				+ " FROM t_auth_item t WHERE t.f_id =? AND t.s_type =?";
		List<AuthItem> ai = dbHelper.getBeanList(sql, AuthItem.class, fid,type);
		return ai;
	}
	
	/**
	 * 根据type查询指标数据
	 * @param pid
	 * @return
	 * @throws SQLException
	 */
	public List<AuthItem> queryAuthItemTwice(String type) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_content as content,t.f_id as fid,t.s_type as type,t.s_number as number ,t.s_create_time as createtime ," +
				  " t.s_major as major ,t.s_standards as standards "
				+ " FROM t_auth_item t WHERE t.s_type =?";
		List<AuthItem> ai = dbHelper.getBeanList(sql, AuthItem.class, type);
		return ai;
		
	}
	
	
	/**
	 * 根据type查询指标数据
	 * @param pid
	 * @return
	 * @throws SQLException
	 */
	public List<AuthItem> queryAuthItemTwice(String type,String major) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_content as content,t.f_id as fid,t.s_type as type,t.s_number as number ,t.s_create_time as createtime," +
				" t.s_major as major ,t.s_standards as standards "
				+ " FROM t_auth_item t WHERE t.s_type ='"+type+"' AND t.s_major ='"+major+"'";
		List<AuthItem> ai = dbHelper.getBeanList(sql, AuthItem.class);
		return ai;
		
	}
	
	
	
	
	/**
	 * 新增指标数据
	 * @param ai
	 * @throws SQLException
	 */
	public void insertAuthItem(AuthItem ai) throws SQLException{
		authItemDao.save(ai);
	}
	
	
	/**
	 * 删除指标数据
	 */
	public void deleteAuthItem(String pid) throws SQLException{
		authItemDao.delete(pid);
	}
	
	/**
	 * 根据一级指标id和专业 查询所有二级指标
	 * @param fid
	 * @param major
	 * @return
	 * @throws SQLException 
	 */
	public List<AuthItem> queryAuthItemsByFidAndMajor(String fid,String major) throws SQLException{
		String sql="SELECT t.p_id as pid,t.s_content as content,t.f_id as fid,t.s_type as type," +
				" t.s_number as number ," +
				" t.s_create_time as createtime," +
				" t.s_major as major ,t.s_standards as standards ,a.s_iswrite as state "+ 
				" FROM t_auth_item as t "
				+ " left join t_auth_item_map as a on t.p_id = a.s_item_id "
				+ " WHERE t.s_type ='1' AND t.f_id=? AND t.s_major=? order by t.s_number";
		List<AuthItem>list=dbHelper.getBeanList(sql, AuthItem.class, fid,major);
		return list;
	}
	
	
	/**
	 * 根据专家id和机构id查询分项id
	 */
	public List<AuthExpertItemMap> queryAuthItemsByExpertIdAndSchoolId(String expertId,String schoolId) throws SQLException{
		String sql = "SELECT a.p_id as pid,a.s_item_id as itemId,a.s_school_id as schoolId,a.s_expert_id as expertId,"+
				 " a.s_group_id as groupId,a.s_state as state"+
				 " FROM t_auth_item_expert_map a where a.s_expert_id = ? AND a.s_school_id = ?";
		List<AuthExpertItemMap> list = dbHelper.getBeanList(sql, AuthExpertItemMap.class, expertId,schoolId);
		return list;
		
	}
	
	/**
	 * 根据fid,type查询指标数据
	 * @param pid
	 * @return
	 * @throws SQLException
	 */
	public AuthItem queryAuthItemByFid1(String fid,String type) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_content as content,t.f_id as fid,t.s_type as type,t.s_number as number ,t.s_create_time as createtime ,t.s_major as major ,t.s_standards as standards"
				+ " FROM t_auth_item t WHERE t.f_id =? AND t.s_type =?";
		AuthItem ai = dbHelper.getBean(sql, AuthItem.class, fid,type);
		return ai;
	}
	
	/**
	 * 根据fid,type,major查询指标数据
	 * @param fid
	 * @param type
	 * @return
	 * @throws SQLException
	 */
	public List<AuthItem> queryAuthItemByFidAndTypeAndMajor(String fid,String type,String major,String schoolId) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_content as content,t.f_id as fid,t.s_type as type,t.s_number as number, t.s_major as major ,t.s_standards as standards," +
				" a.s_value as level, a.s_main_problem as mainProblem"
				+ " FROM t_auth_item as t left join t_auth_item_expert_data as a on t.p_id = a.s_index_id " +
				"WHERE t.f_id = '"+fid+"' AND t.s_type ='"+type+"' and t.s_major = '"+major+"' AND a.s_school_id = '"+schoolId+"' order by t.s_number";
		List<AuthItem> ai = dbHelper.getBeanList(sql, AuthItem.class);
		return ai;
	}
	
	/**
	 * 根据fid,type,major查询指标数据
	 * @param fid
	 * @param type
	 * @return
	 * @throws SQLException
	 */
	public AuthItem queryAuthItemByFidAndTypeAndMajor1(String fid,String type,String major,String schoolId) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_content as content,t.f_id as fid,t.s_type as type,t.s_number as number ,t.s_create_time as createtime ,t.s_major as major ,t.s_standards as standards, a.s_expert_id as expertId"
				+ " FROM t_auth_item t left join t_auth_item_expert_map as a on a.s_item_id = t.f_id WHERE t.p_id =? AND t.s_type =? and t.s_major = ? AND a.s_school_id =? order by t.s_number";
		AuthItem ai = dbHelper.getBean(sql, AuthItem.class, fid,type,major,schoolId);
		return ai;
	}
	
	/**
	 * 根据fid,type,major查询指标数据
	 * @param fid
	 * @param type
	 * @return
	 * @throws SQLException
	 */
	public List<AuthItem> queryAuthItemByFidAndTypeAndMajor2(String fid,String type,String major) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_content as content,t.f_id as fid,t.s_type as type,t.s_number as number, t.s_major as major ,t.s_standards as standards" +
				" FROM t_auth_item as t WHERE t.f_id =? AND t.s_type =? and t.s_major = ? order by t.s_number";
		List<AuthItem> ai = dbHelper.getBeanList(sql, AuthItem.class, fid,type,major);
		return ai;
	}
	
	/**
	 * 查询一级指标和绑定的专家
	 */
	public List<AuthItem> queryAuthItemAndExpert(String schoolId) throws SQLException{
		String sql = "select a.p_id as pid, a.s_content as content, a.s_number as number, b.s_state as state, c.s_expert_name as expertName " +
				"from t_auth_item as a left join t_auth_item_expert_map as b on a.p_id = b.s_item_id " +
				"left join t_auth_expert as c on b.s_expert_id = c.p_id where a.s_type = 0 and b.s_school_id = ? order by a.s_number";
		return dbHelper.getBeanList(sql, AuthItem.class, schoolId);
		
	}
	
	/**
	 * 汇总查询
	 */
	public List<AuthItem> queryAuthCollect(String schoolId,String itemId) throws SQLException{
		String sql = "select a.p_id as pid, a.s_content as content, a.s_number as number, b.s_main_problem as mainProblem, b.s_value as level, b.s_review as review " +
				"from t_auth_item as a left join t_auth_item_expert_data as b on a.p_id = b.s_index_id " +
				"where a.s_type = 1 and b.s_school_id = ? AND b.s_type = 1 AND b.s_item_id = ? order by a.s_number";
		return dbHelper.getBeanList(sql, AuthItem.class, schoolId,itemId);
		
	}
	
	/**
	 * 查询一级指标
	 * @throws SQLException 
	 */
	public List<AuthItem> queryAuthItemIndex() throws SQLException{
		String sql = "select * from t_auth_item as t where t.s_type = 0";
		return this.dbHelper.getBeanList(sql, AuthItem.class);
	}
}
